﻿use master
go

create database TestDB
on
(
	name='TestDB',
	filename='D:\数据库\TestDB.mdf',
	size=5mb,
	maxsize=100mb,
	filegrowth=10%
)
log on
(
	name='TestDB_log',
	filename='D:\数据库\TestDB_log.ldf',
	size=5mb,
	maxsize=100mb,
	filegrowth=10%
)

use TestDB
go

create table typeInfo
(
	typeId int primary key identity(1,1),
	typeName varchar(10) not null
)

create table loginInfo
(
	LoginId int primary key identity(1,1),
	LoginName char(10) not null unique,
	LoginPwd char(20) default(123456) not null,
	Sex char(1),
	Birthday char(10),
	MemType varchar(10)		 
)






create database company
on
(
	name='company',
	filename='D:\数据库\company.mdf',
	size=5mb,
	maxsize=100mb,
	filegrowth=10%
)
log on
(
	name='company_log',
	filename='D:\数据库\company_log.ldf',
	size=5mb,
	maxsize=100mb,
	filegrowth=10%
)

use company
go

create table sectionInfo
(
	sectionID int primary key identity(1,1),
	sectionName varchar(10) not null
)

create table userInfo
(
	userNo int primary key identity(1,1) not null,
	userName  varchar(10) not null unique check(len(userName)>4),
	userSex varchar(2) check(userSex='男' or userSex='女') not null,
	userAge int not null check(userAge>=1 and userAge<=100),--范围在1-100之间
	userAddress varchar(50) default('湖北'),
	userSection int references sectionInfo(sectionID)
)

create table workInfo
(
	workId int primary key not null,
	userId int references userInfo(userNo) not null,
	workTime datetime not null,
	workDescription varchar(40) check(workDescription='早退'or 
	workDescription='旷工'or workDescription='病假' or workDescription='事假') not null
)

create database HomeInfo
on
(
	name='HomeInfo',
	filename='D:\数据库\HomeInfo.mdf',
	size=5mb,
	maxsize=100mb,
	filegrowth=10%
)
log on
(
	name='HomeInfo_log',
	filename='D:\数据库\HomeInfo_log.ldf',
	size=5mb,
	maxsize=100mb,
	filegrowth=10%
)

use HomeInfo
go

create table TbIUser
(
	UserID int primary key identity(1,1),
	UserName nvarchar(10) not null,
	UserTel nvarchar(11) not null check(len(UserTel)=11)
)

create table tblHouseType
(
	TypelD int primary key identity(1,1),
	Typname nvarchar(10) not null check(Typname='' or Typname='ƽ' or Typname='ͨסլ' or Typname='')
)

create table TbIQx
(
	QxID int primary key identity(1,1),
	Qxname nvarchar(10) not null check(Qxname='' or Qxname='' or Qxname='')
)

create table TblHouseInfo
(
	HomeID int primary key identity(1,1),
	HomeDesc nvarchar(10),
	UserID int foreign key references  TbIUser(UserID),
	Homezj int not null,
	HomeShi int not null,
	HomeTing int not null,
	TypeID int foreign key references tblHouseType(TypelD),
	QxID int foreign key references TbIQx(Qxid)
)